import 'package:flutter/material.dart';
import '../services/database_service.dart';
import 'dart:developer' as developer;

class DatabaseCheckPage extends StatefulWidget {
  const DatabaseCheckPage({super.key});

  @override
  State<DatabaseCheckPage> createState() => _DatabaseCheckPageState();
}

class _DatabaseCheckPageState extends State<DatabaseCheckPage> {
  final DatabaseService _databaseService = DatabaseService();
  String _dbInfo = '正在检查数据库...';
  bool _isLoading = true;
  bool _isFixing = false;
  String _fixResult = '';

  @override
  void initState() {
    super.initState();
    _checkDatabase();
  }

  Future<void> _checkDatabase() async {
    try {
      setState(() {
        _isLoading = true;
        _dbInfo = '正在检查数据库...';
      });

      // 初始化数据库
      await _databaseService.init();

      // 检查questions表结构
      final tableInfo = await _checkTableStructure();

      setState(() {
        _dbInfo = tableInfo;
        _isLoading = false;
      });
    } catch (e) {
      setState(() {
        _dbInfo = '检查数据库时出错: $e';
        _isLoading = false;
      });
      developer.log('检查数据库时出错', error: e);
    }
  }

  Future<void> _fixDatabase() async {
    setState(() {
      _isFixing = true;
      _fixResult = '正在修复数据库...';
    });
    
    try {
      // 检查是否存在key_point列
      final questionTableResult = await _databaseService.rawQuery(
          'PRAGMA table_info(${DatabaseService.questionsTable})');
      final hasKeyPoint = questionTableResult.any((column) => column['name'] == 'key_point');
      
      if (!hasKeyPoint) {
        // 添加key_point列
        await _databaseService.rawQuery(
            'ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN key_point TEXT DEFAULT ""');
        
        setState(() {
          _fixResult = '成功添加key_point列！';
        });
      } else {
        setState(() {
          _fixResult = 'key_point列已存在，无需修复。';
        });
      }
      
      // 检查笔记表中的字段名
      final notesTableResult = await _databaseService.rawQuery(
          'PRAGMA table_info(${DatabaseService.notesTable})');
      
      // 检查是否存在created_at和updated_at列
      final hasCreatedAt = notesTableResult.any((column) => column['name'] == 'created_at');
      final hasUpdatedAt = notesTableResult.any((column) => column['name'] == 'updated_at');
      
      // 检查是否存在createdAt和updatedAt列（驼峰命名法）
      final hasCreatedAtCamel = notesTableResult.any((column) => column['name'] == 'createdAt');
      final hasUpdatedAtCamel = notesTableResult.any((column) => column['name'] == 'updatedAt');
      
      if (!hasCreatedAt && hasCreatedAtCamel || !hasUpdatedAt && hasUpdatedAtCamel) {
        // 需要修复笔记表中的字段名
        await _databaseService.rawQuery('''
          CREATE TABLE ${DatabaseService.notesTable}_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            category TEXT,
            tags TEXT,
            image_url TEXT,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
          )
        ''');
        
        // 尝试从旧表复制数据到新表
        try {
          // 使用驼峰命名法的字段名
          await _databaseService.rawQuery('''
            INSERT INTO ${DatabaseService.notesTable}_new 
            SELECT id, title, content, category, tags, image_url, createdAt, updatedAt 
            FROM ${DatabaseService.notesTable}
          ''');
        } catch (e) {
          developer.log('使用驼峰命名法字段名复制数据失败: $e');
          
          try {
            // 如果失败，尝试使用下划线命名法的字段名
            await _databaseService.rawQuery('''
              INSERT INTO ${DatabaseService.notesTable}_new 
              SELECT id, title, content, category, tags, image_url, created_at, updated_at 
              FROM ${DatabaseService.notesTable}
            ''');
          } catch (e) {
            developer.log('复制数据失败: $e');
            throw Exception('复制数据失败: $e');
          }
        }
        
        // 删除旧表
        await _databaseService.rawQuery('DROP TABLE ${DatabaseService.notesTable}');
        
        // 重命名新表
        await _databaseService.rawQuery('ALTER TABLE ${DatabaseService.notesTable}_new RENAME TO ${DatabaseService.notesTable}');
        
        setState(() {
          _fixResult = '成功修复笔记表字段名！';
        });
      } else if (hasCreatedAt && hasUpdatedAt) {
        setState(() {
          _fixResult += '\n笔记表字段名已正确使用下划线命名法，无需修复。';
        });
      }
    } catch (e) {
      setState(() {
        _fixResult = '修复数据库时出错: $e';
      });
      developer.log('修复数据库时出错', error: e);
    } finally {
      setState(() {
        _isFixing = false;
      });
      // 刷新数据库信息
      await _checkDatabase();
    }
  }

  Future<String> _checkTableStructure() async {
    try {
      // 执行一个简单的查询来获取表结构信息
      final result = await _databaseService.rawQuery(
          'PRAGMA table_info(${DatabaseService.questionsTable})');

      // 构建表结构信息字符串
      final buffer = StringBuffer();
      buffer.writeln('questions表结构:');
      
      for (final column in result) {
        buffer.writeln('${column['name']} (${column['type']})');
      }

      // 检查是否存在key_point列
      final hasKeyPoint = result.any((column) => column['name'] == 'key_point');
      buffer.writeln('\n是否存在key_point列: ${hasKeyPoint ? '是' : '否'}');

      return buffer.toString();
    } catch (e) {
      return '获取表结构信息失败: $e';
    }
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('数据库检查'),
      ),
      body: Padding(
        padding: const EdgeInsets.all(16.0),
        child: Column(
          crossAxisAlignment: CrossAxisAlignment.start,
          children: [
            Text(
              '数据库信息',
              style: Theme.of(context).textTheme.headlineSmall,
            ),
            const SizedBox(height: 16),
            if (_isLoading)
              const Center(child: CircularProgressIndicator())
            else
              Expanded(
                child: SingleChildScrollView(
                  child: Text(_dbInfo),
                ),
              ),
            const SizedBox(height: 16),
            ElevatedButton(
              onPressed: _isLoading ? null : _checkDatabase,
              child: const Text('刷新'),
            ),
            const SizedBox(height: 16),
            ElevatedButton(
              onPressed: _isLoading || _isFixing ? null : _fixDatabase,
              style: ElevatedButton.styleFrom(
                backgroundColor: Colors.orange,
              ),
              child: const Text('修复数据库结构'),
            ),
            if (_fixResult.isNotEmpty)
              Padding(
                padding: const EdgeInsets.only(top: 16.0),
                child: Text(_fixResult, 
                  style: TextStyle(color: _fixResult.contains('成功') ? Colors.green : Colors.red),
                ),
              ),
          ],
        ),
      ),
    );
  }
}